package com.situ.scientific.dao.impl;

import com.situ.scientific.dao.IResearchersDao;
import com.situ.scientific.pojo.Researchers;
import com.situ.scientific.pojo.query.ResearchersQuery;
import com.situ.scientific.pojo.vo.ResearchIdVo;
import com.situ.scientific.pojo.vo.ResearchersVo;
import com.situ.scientific.util.JDBCUtils;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.ArrayList;
import java.util.List;

public class ResearchersDaoImpl implements IResearchersDao {
    JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());
    @Override
    public int totalCount(ResearchersQuery researchersQuery) {
        List<Object> queryList = new ArrayList<>();
        StringBuilder sql = new StringBuilder(
                "SELECT COUNT(*) " +
                        "FROM researchers r " +
                        "JOIN project p ON p.researchers_id=r.id " +
                        "JOIN title t ON r.title_no = t.id " +
                        "JOIN examine e ON p.id = e.project_no " +
                        "WHERE 1=1 " // 添加 WHERE 子句以处理动态条件
        );

        String queryName = researchersQuery.getName();
        if (queryName != null && !"".equals(queryName)) {
            sql.append(" AND r.name LIKE ?");
            queryList.add("%" + queryName + "%");
        }

        String facultyList = researchersQuery.getFaculty();
        if (facultyList != null && !"".equals(facultyList)) {
            sql.append(" AND r.faculty = ?");
            queryList.add(facultyList);
        }

        // 将 StringBuilder 转换为 String
        return template.queryForObject(sql.toString(), Integer.class, queryList.toArray());
    }

    @Override
    public List<ResearchersVo> selectByPage(ResearchersQuery researchersQuery) {
        List<Object> queryList = new ArrayList<>();
        String sql = "SELECT r.id,r.name, r.faculty, p.name AS projectName, t.name AS titleName, r.e_mail, e.approval_progress AS approvalProgress, e.commit\n" +
                "FROM researchers r\n" +
                "JOIN project p ON p.researchers_id=r.id \n" +
                "JOIN title t ON t.id=r.title_no \n" +
                "JOIN examine e ON p.id = e.project_no WHERE 1=1";

        String queryName = researchersQuery.getName();
        if (queryName != null && !"".equals(queryName)) {
            sql += " AND r.name LIKE ?";
            queryList.add("%" + queryName + "%");
        }
        String facultyList = researchersQuery.getFaculty();
        if (facultyList != null && !"".equals(facultyList)) {
            sql += " AND faculty=?";
            queryList.add(facultyList);
        }
        sql += " ORDER BY r.id ASC LIMIT ?,?";
        int offset = (researchersQuery.getPage() - 1) * researchersQuery.getLimit();
        queryList.add(offset);
        queryList.add(researchersQuery.getLimit());
        return template.query(sql,new BeanPropertyRowMapper<>(ResearchersVo.class),queryList.toArray());
    }

    @Override
    public void add(Researchers researchers) {
        String sql = "INSERT INTO researchers(name,)";
        template.update(sql);
    }

    @Override
    public Researchers login(String name, String password) {
        String sql = "SELECT id,name,password,role,status FROM researchers WHERE name=? AND password=?";
        return template.queryForObject(sql, new BeanPropertyRowMapper<>(Researchers.class), name, password);
    }

    @Override
    public ResearchIdVo selectById(String id) {
        System.out.println(id);
        String sql = "SELECT \n" +
                "    r.id,\n" +
                "    r.name,\n" +
                "    r.faculty,\n" +
                "    COUNT(p.researchers_id) AS project_count,\n" +
                "    t.name AS title_name,\n" +
                "    r.e_mail\n" +
                "FROM \n" +
                "    researchers r\n" +
                "LEFT JOIN \n" +
                "    project p ON r.id = p.researchers_id\n" +
                "LEFT JOIN \n" +
                "    title t ON r.title_no = t.id\n" +
                "WHERE \n" +
                "    r.id = ?\n" +
                "GROUP BY \n" +
                "    r.id, r.name, r.faculty, r.e_mail, t.name;";
        return template.queryForObject(sql,new BeanPropertyRowMapper<>(ResearchIdVo.class),id);
    }
}
